use async_std::task;
use sqlx::prelude::*;
use sqlx::Pool;
use std::env;
use sqlx::sqlite::SqlitePoolOptions;
use dotenv::dotenv;
use sqlx::SqliteConnection;
use crate::item;
// use log::info;
async fn get_todos(database_url:String) -> Result<(), sqlx::Error> {
    // let conn = SqliteConnection::connect(&database_url).await?;
    println!("before connect");
    let pool = SqlitePoolOptions::new().connect(&database_url).await?;
    // let sql="INSERT INTO todos_todo ( text ,completed)VALUES ( $1,$2)";
    // let count=sqlx::query(sql).bind("test3").bind(false).execute(&pool).await?;
    // println!("{}",count);
    // println!("here");
    // let sql="select id,yonghu,yiqixinghao,yiqibh,baoxiang,shenhe,yujifahuo_date,hetongbh,addr,channels,tiaoshi_date,method from parts_contact limit 3";
    // let sql = "select id,text,completed from todos_todo";
    println!("before query");
    let recs = sqlx::query("select id,yonghu,yiqixinghao,yiqibh,baoxiang,shenhe,yujifahuo_date,hetongbh,addr,channels,tiaoshi_date,method from parts_contact limit 3").fetch_all(&pool)
        .await?;
    println!("here");
    for rec in recs {
        let id:i64=rec.get(0);
        let yh:String=rec.get(1);
        println!("id={},yonghu={}",id,yh);
    }
    Ok(())
}
async fn add_todo(database_url:String,text:String,completed:bool)-> Result<i32, sqlx::Error>  {
    let pool = SqlitePoolOptions::new().connect(&database_url).await?;
    let sql="INSERT INTO todos_todo ( text ,completed)VALUES ( $1,$2) returning *;";
    let count=sqlx::query(sql).bind(&text).bind(completed).execute(&pool).await?;
    // println!("{}",count);
    println!("here");
    // let sql = "select last_insert_rowid()";
    // let mut cursor = sqlx::query(sql).fetch(&pool);
    // while let Some(row) = cursor.try_next().await? {
    //     let id: i32 = row.get(0);
    //     println!("last_rowid={}",id);
    // }
    Ok(0)
}
async fn get_contacts(database_url:String)
{ 
    let pool_r = SqlitePoolOptions::new().connect(&database_url).await;
    match pool_r{
        Ok(pool) =>{
            println!("connect ok");
             // let result = Contact::get(db_pool.get_ref(),info1.start,info1.limit).await;
            let result = item::Contact::get(&pool,0,10).await;
            match result {
                Ok(items) =>{
                    for contact in items{
                        println!("{},{},{}",contact.id,contact.yujifahuo_date,contact.work_month);
                    }
                    println!("get ok");
                },
                _ => {
                    println!("get error")
                },
            }
        },
        _ => {
            println!("connect error")
        },
    }
}
pub fn main(){
    dotenv().ok();
    println!("begin sqlx1 main");
    let database_url =
        env::var("DATABASE_URL").expect("DATABASE_URL is not set in .env file");
    // task::block_on(add_todo(database_url.clone(), String::from("0000"),false));
    // task::block_on(get_contacts(database_url.clone()));
    task::block_on(get_todos(database_url.clone()));
    // let r=add_todo(database_url.clone(), String::from("0000"),false);
    // match r {
    //     Ok(i) => {
    //         println!("{}",i);
    //     },
    //     Err(_) => {
    //         println!("err");        
    //     }
    // }
    //  match f {
    //     Ok(file) => {
    //         println!("File opened successfully.");
    //     },
    //     Err(err) => {
    //         println!("Failed to open the file.");
    //     }
    // }
    // task::block_on(get_todos(database_url));
}
